﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entidad;
using System.Data.SqlClient;
using System.Configuration;

namespace Dao
{
    public class NotasQueryDao
    {

        private static SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AcademicoConnectionString"].ConnectionString);
        public static SqlCommand cmd;

        public NotasQueryDao()
        {

        }

        public static List<NotasQuery> Informe(decimal? promedioDesde, decimal? promedioHasta, int? curso, int? materia)
        {
            List<NotasQuery> listNotas = new List<NotasQuery>();
            string sql = @"SELECT AVG(Cast(np.nota as decimal(18,2))) as promedio, m.nombre, c.numAula, a.apellido, a.nombre, d.apellido, d.nombre
                            FROM NotaParciales np INNER JOIN
                         Asignatura asi ON np.codAsignatura = asi.codAsignatura INNER JOIN
                         Materia m ON asi.codMateria = m.codMateria INNER JOIN
                         Alumno a ON np.legajo = a.legajo INNER JOIN
                         Curso c ON asi.codCurso = c.codCurso INNER JOIN
                         Docente d on  asi.legDocente=d.legajo
                                where 1 = 1";
            if (curso != null)
                sql += " AND c.numAula = @NumAula";
            if (materia != null)
                sql += " AND m.codMateria = @CodMateria";

            sql += " GROUP BY m.nombre, c.numAula, a.apellido,a.nombre, d.apellido, d.nombre";

            if (promedioDesde != null)
            {
                sql += " HAVING AVG(Cast(np.nota as decimal(18,2))) > @PromedioDesde";
                if (promedioHasta != null)
                    sql += " AND AVG(Cast(np.nota as decimal(18,2))) <= @PromedioHasta";
            }
            else
            {
                if (promedioHasta != null)
                    sql += " HAVING AVG(Cast(np.nota as decimal(18,2))) <= @PromedioHasta";
            }

            try
            {
                cn.Open();
                cmd = new SqlCommand(sql, cn);
                if (curso != null)
                    cmd.Parameters.AddWithValue("@NumAula", curso.Value);
                if (materia != null)
                    cmd.Parameters.AddWithValue("@CodMateria", materia.Value);
                if (promedioDesde != null)
                    cmd.Parameters.AddWithValue("@PromedioDesde", promedioDesde.Value);
                if (promedioHasta != null)
                    cmd.Parameters.AddWithValue("@PromedioHasta", promedioHasta.Value);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    NotasQuery notaQ = new NotasQuery();
                    notaQ.apenomA = dr[3].ToString() + ", " + dr[4].ToString();
                    notaQ.apenomD = dr[5].ToString() + ", " + dr[6].ToString();
                    notaQ.materia = dr[1].ToString();
                    notaQ.numAula = (int)dr[2];
                    notaQ.promedio = decimal.Parse(dr[0].ToString());
                    listNotas.Add(notaQ);
                }
                dr.Close();
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == System.Data.ConnectionState.Open)
                    cn.Close();
                throw new ApplicationException("ERROR al consultar.\n Problemas:" + ex.Message);
            }
            return listNotas;

        }
    }
}
